花了點時間完成昨日進度，加油!菜鳥!

608. Tree Node

Example 1:

+-------------+------+
| Column Name | Type |
+-------------+------+
| id          | int  |
| p_id        | int  |
+-------------+------+
id is the primary key column for this table.
Each row of this table contains information about the id of a node and the id of its parent node in a tree.
The given structure is always a valid tree.

Each node in the tree can be one of three types:

"Leaf": if the node is a leaf node.
"Root": if the node is the root of the tree.
"Inner": If the node is neither a leaf node nor a root node.
Write an SQL query to report the type of each node in the tree.

Return the result table in any order.

The query result format is in the following example.

Input: 
Tree table:
+----+------+
| id | p_id |
+----+------+
| 1  | null |
| 2  | 1    |
| 3  | 1    |
| 4  | 2    |
| 5  | 2    |
+----+------+
Output: 
+----+-------+
| id | type  |
+----+-------+
| 1  | Root  |
| 2  | Inner |
| 3  | Leaf  |
| 4  | Leaf  |
| 5  | Leaf  |
+----+-------+
Explanation: 
Node 1 is the root node because its parent node is null and it has child nodes 2 and 3.
Node 2 is an inner node because it has parent node 1 and child node 4 and 5.
Nodes 3, 4, and 5 are leaf nodes because they have parent nodes and they do not have child nodes.
Example 2:

Input: 
Tree table:
+----+------+
| id | p_id |
+----+------+
| 1  | null |
+----+------+
Output: 
+----+-------+
| id | type  |
+----+-------+
| 1  | Root  |
+----+-------+
Explanation: If there is only one node on the tree, you only need to output its root attributes.

這一題需要幫這個樹狀圖做命名，
1.NULL值改成Root
2.底下有連結的為Inner
3.底下無連結的為Leaf
用一些IF THEN的邏輯就能解出來，
** 遇到的問題是('IS' 不等於 '=')，在刷題時的小記錄，
還有CASE WHEN THEN END在用字上需要記一下

SELECT id,
(CASE
    WHEN p_id IS null THEN 'Root'
    WHEN id IN (SELECT p_id FROM Tree )THEN 'Inner'
    ELSE 'Leaf'
    END)AS type

FROM Tree 

176. Second Highest Salary

+-------------+------+
| Column Name | Type |
+-------------+------+
| id          | int  |
| salary      | int  |
+-------------+------+
id is the primary key column for this table.
Each row of this table contains information about the salary of an employee.

Write an SQL query to report the second highest salary from the Employee table. If there is no second highest salary, the query should report null.

The query result format is in the following example.

Example 1:

Input: 
Employee table:
+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+
Output: 
+---------------------+
| SecondHighestSalary |
+---------------------+
| 200                 |
+---------------------+
Example 2:

Input: 
Employee table:
+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
+----+--------+
Output: 
+---------------------+
| SecondHighestSalary |
+---------------------+
| null                |
+---------------------+

這一題要列出第2高的薪資(salary欄位)，如果沒有則回報NULL，
這幾天從SQL的結構中學到一些計算都要在SELECT區塊進行，
就嘗試用max()找最大值再補條件，幸好他只會列第二大的值，而不是全部列出。

SELECT max(salary) AS SecondHighestSalary
FROM Employee 
WHERE salary < (SELECT max(salary) FROM Employee)

我就菜

1965. Employees With Missing Information

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| employee_id | int     |
| name        | varchar |
+-------------+---------+
employee_id is the primary key for this table.
Each row of this table indicates the name of the employee whose ID is employee_id.

Table: Salaries

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| employee_id | int     |
| salary      | int     |
+-------------+---------+
employee_id is the primary key for this table.
Each row of this table indicates the salary of the employee whose ID is employee_id.

Write an SQL query to report the IDs of all the employees with missing information. The information of an employee is missing if:

The employee's name is missing, or
The employee's salary is missing.
Return the result table ordered by employee_id in ascending order.

The query result format is in the following example.

Example 1:

Input: 
Employees table:
+-------------+----------+
| employee_id | name     |
+-------------+----------+
| 2           | Crew     |
| 4           | Haven    |
| 5           | Kristian |
+-------------+----------+
Salaries table:
+-------------+--------+
| employee_id | salary |
+-------------+--------+
| 5           | 76071  |
| 1           | 22517  |
| 4           | 63539  |
+-------------+--------+
Output: 
+-------------+
| employee_id |
+-------------+
| 1           |
| 2           |
+-------------+
Explanation: 
Employees 1, 2, 4, and 5 are working at this company.
The name of employee 1 is missing.
The salary of employee 2 is missing.

這題要找出缺少資料的員工，觀察Employees跟Salaries兩個表，
能看出employee_id 1、2 兩筆資料會缺name或salary的內容，
一開始的想法是把表合在一起，直接取只有一筆的1跟2出來，
但卡在合成一表後，沒辦法在最外層用DISTINCT只取出現過一次的值，
在GOOGLE後看到大神的寫法，這邊直接進行說明，
先比對出employee_id欄位，於Employees表中不在Salaries中值
再比對出employee_id欄位，於Salaries表中不在Employees中值
兩個結果用UNION連在一起，最後ORDER BY進行排序

SELECT employee_id 
FROM Employees 
WHERE employee_id NOT IN (SELECT employee_id FROM Salaries)
UNION
SELECT employee_id 
FROM Salaries 
WHERE employee_id NOT IN (SELECT employee_id FROM Employees)

ORDER BY employee_id;

1795. Rearrange Products Table

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| product_id  | int     |
| store1      | int     |
| store2      | int     |
| store3      | int     |
+-------------+---------+
product_id is the primary key for this table.
Each row in this table indicates the product's price in 3 different stores: store1, store2, and store3.
If the product is not available in a store, the price will be null in that store's column.
 
Write an SQL query to rearrange the Products table so that each row has (product_id, store, price). If a product is not available in a store, do not include a row with that product_id and store combination in the result table.

Return the result table in any order.

The query result format is in the following example.

Example 1:

Input: 
Products table:
+------------+--------+--------+--------+
| product_id | store1 | store2 | store3 |
+------------+--------+--------+--------+
| 0          | 95     | 100    | 105    |
| 1          | 70     | null   | 80     |
+------------+--------+--------+--------+
Output: 
+------------+--------+-------+
| product_id | store  | price |
+------------+--------+-------+
| 0          | store1 | 95    |
| 0          | store2 | 100   |
| 0          | store3 | 105   |
| 1          | store1 | 70    |
| 1          | store3 | 80    |
+------------+--------+-------+
Explanation: 
Product 0 is available in all three stores with prices 95, 100, and 105 respectively.
Product 1 is available in store1 with price 70 and store3 with price 80. The product is not available in store2.

這題要把store1.2.3整合在一起，除了分數NULL的不列入，其他分數都寫在price欄位中，
一開始想全部寫一個改名的方式，但輸出的樣子完全不對，就先註解起來當記錄，
在W3S中查詢到是用UNION ALL進行連結，
要用UNION ALL是因為UNION本身會排除重複值，

/*SELECT product_id ,
        'store1' AS 'store'  ,
        'store2' AS 'store'  ,
        'store3' AS 'store'  ,
        store1 AS price  ,
        store2 AS price  ,
        store3 AS price 
        FROM Products WHERE store1 IS NOT NULL;
*/

SELECT product_id ,
        'store1' AS 'store',
         store1 AS price
        FROM Products WHERE store1 IS NOT NULL

UNION ALL

SELECT product_id ,
        'store2' AS 'store',
         store2 AS price
        FROM Products WHERE store2 IS NOT NULL

UNION ALL

SELECT product_id ,
        'store3' AS 'store',
         store3 AS price
        FROM Products WHERE store3 IS NOT NULL;